package bancosys.tec.utils.db.cleaner;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import jmine.tec.utils.UtilsMessages;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * Cleaner para banco de dados DB2. <br>
 * Limpa tabelas e sequences.
 */
public class DB2DbCleaner extends DbCleaner {
    private static Log log = LogFactory.getLog(DB2DbCleaner.class);

    private String schemaName;

    /**
     * Construtor.
     * 
     * @param conn conexão sql.
     * @param schemaName o schema em que o banco foi criado.
     */
    public DB2DbCleaner(Connection conn, String schemaName) {
        super(conn);
        this.schemaName = schemaName;
    }

    /**
     * Drop o banco de dados (tabelas e sequencias).
     */
    @Override
    public void drop() {
        try {
            for (String view : this.findViews()) {
                Statement stm = this.getConn().createStatement();
                stm.executeUpdate("DROP VIEW " + view);
                stm.close();
            }

            for (String table : this.findTables()) {
                Statement stm = this.getConn().createStatement();
                stm.executeUpdate("DROP TABLE " + table);
                stm.close();
            }

            for (String seq : this.findSequences()) {
                Statement stm = this.getConn().createStatement();
                stm.executeUpdate("DROP SEQUENCE " + seq);
                stm.close();
            }
        } catch (SQLException e) {
            throw new CouldNotDropException(UtilsMessages.DB_COULD_NOT_DROP.create(), e);
        }
    }

    /**
     * Pesquisa as sequences para o schemaName no catalogo do banco de dados.
     * 
     * @return Lista com os nomes das sequences.
     * @throws SQLException Erro ao acessar os dados.
     */
    private List<String> findSequences() throws SQLException {
        List<String> sequences = new ArrayList<String>();
        String query = "SELECT SEQNAME FROM SYSCAT.SEQUENCES WHERE SEQSCHEMA='" + this.schemaName + "'";

        Statement stm = this.getConn().createStatement();
        ResultSet rs = stm.executeQuery(query);
        while (rs.next()) {
            sequences.add(rs.getString(1));
        }
        stm.close();
        return sequences;
    }

    /**
     * Deleta todos os dados.
     */
    @Override
    public void clean() {
        try {
            this.getConn().setAutoCommit(false);

            this.disableAllConstraints();
            this.deleteAll();
            this.enableAllConstraints();
        } catch (SQLException e) {
            throw new CouldNotDropException(UtilsMessages.DB_COULD_NOT_DROP.create(), e);
        }
    }

    /**
     * Enable das constraints.
     * 
     * @throws SQLException falha ao acessar o banco de dados.
     */
    public void enableAllConstraints() throws SQLException {
        log.info("Iniciando enable de constraints das tabelas");
        List<String> tables = this.findTables();
        for (String table : tables) {
            List<String> constraints = this.findConstraints(table);
            for (String constraint : constraints) {
                Statement stm = this.getConn().createStatement();
                stm.executeUpdate("ALTER TABLE " + table + " ALTER FOREIGN KEY " + constraint + " ENFORCED");
                stm.close();
                this.getConn().commit();
            }
        }
        log.info("Finalizado enable de constraints das tabelas");
    }

    /**
     * Disable de todas as constraints.
     * 
     * @throws SQLException falha ao acessar o banco de dados.
     */
    public void disableAllConstraints() throws SQLException {
        log.info("Iniciando disable de constraints das tabelas");
        List<String> tables = this.findTables();
        for (String table : tables) {
            List<String> constraints = this.findConstraints(table);
            for (String constraint : constraints) {
                Statement stm = this.getConn().createStatement();
                stm.executeUpdate("ALTER TABLE " + table + " ALTER FOREIGN KEY " + constraint + " NOT ENFORCED");
                stm.close();
                this.getConn().commit();
            }
        }
        log.info("Finalizado disable de constraints das tabelas");
    }

    /**
     * \ Pesquisa todas as constraints da tabela no catalogo do banco de dados.
     * 
     * @param tableName o nome da tabela.
     * @return Lista com os nomes das contraints.
     * @throws SQLException falha ao acessar o banco de dados.
     */
    private List<String> findConstraints(String tableName) throws SQLException {
        List<String> constraints = new ArrayList<String>();

        StringBuffer query = new StringBuffer();
        query.append("select constraint_name from sysibm.table_constraints ");
        query.append("where constraint_type like '%FOREIGN KEY%' ");
        query.append("  and table_schema='").append(this.schemaName).append("' ");
        query.append("  and table_name ='").append(tableName).append("'");

        Statement stm = this.getConn().createStatement();
        ResultSet rs = stm.executeQuery(query.toString());
        while (rs.next()) {
            constraints.add(rs.getString(1));
        }
        stm.close();
        return constraints;
    }

    /**
     * Pesquisa todas as tabelas do schema no catalogo do banco de dados.
     * 
     * @return Lista com nome das tabelas.
     * @throws SQLException falha no banco de dados.
     */
    private List<String> findTables() throws SQLException {
        List<String> tables = new ArrayList<String>();
        String query = "SELECT TABLE_NAME FROM SYSIBM.TABLES where table_schema='" + this.schemaName + "' and TABLE_TYPE like '%TABLE%'";

        Statement stm = this.getConn().createStatement();
        ResultSet rs = stm.executeQuery(query);
        while (rs.next()) {
            tables.add(rs.getString(1));
        }
        stm.close();
        return tables;
    }

    /**
     * Pesquisa todas as views do schema no catalogo do banco de dados.
     * 
     * @return Lista com nome das tabelas.
     * @throws SQLException falha no banco de dados.
     */
    private List<String> findViews() throws SQLException {
        List<String> tables = new ArrayList<String>();
        String query = "SELECT TABLE_NAME FROM SYSIBM.TABLES where table_schema='" + this.schemaName + "' and TABLE_TYPE like '%VIEW%'";

        Statement stm = this.getConn().createStatement();
        ResultSet rs = stm.executeQuery(query);
        while (rs.next()) {
            tables.add(rs.getString(1));
        }
        stm.close();
        return tables;
    }

    /**
     * Deleta todas as tabelas.
     * 
     * @throws SQLException falha ao deletar.
     */
    public void deleteAll() throws SQLException {
        log.info("Iniciando deleção de registros das tabelas");
        List<String> tables = this.findTables();
        for (String table : tables) {
            Statement stm = this.getConn().createStatement();
            stm.executeUpdate("DELETE FROM " + table);
            stm.close();
            this.getConn().commit();
        }
        log.info("Finalizada deleção de registros das tabelas");
    }

}
